package cc.bookcloud.tool.ocr.common.sqlite;
/*==========================================================================
 * Copyright (C) Wit2Cloud Co.,Ltd
 * All Rights Reserved.
 * Created By 开源学社
 ==========================================================================*/

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.dialect.impl.Sqlite3Dialect;
import com.alibaba.fastjson.JSON;
import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteDataSource;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * @author John Goo
 * @version 1.0
 * @Desc: TODO
 * @ClassName: Sqlite3Demo
 * @history v1.0
 */
public class Sqlite3Demo {


    private SQLiteDataSource ds;

    public Sqlite3Demo() {
        String path = Thread.currentThread().getContextClassLoader().getResource("").getPath();
        //path = path.replaceFirst("/","");
        System.out.println("======= path:"+path);
       // String dbPath = "/D:/my-workspace/lowcode-research/Java/release-assist-tool/target/classes/x_release.db";
        String dbPath = path+"/x_release.db";
        String url = "jdbc:sqlite:"+dbPath;
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        SQLiteConfig conf = new SQLiteConfig();
        conf.setSharedCache(true);
        conf.setSynchronous(SQLiteConfig.SynchronousMode.OFF);
        try {
            conf.createConnection(url);
            ds = new SQLiteDataSource(conf);
            ds.setUrl(url);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // conf.setJournalMode(JournalMode.MEMORY);

    }

    /**
     *
     */
    public void test() {
        //Connection conn = null;
        try {
            System.out.printf("====conn:" + ds.getConnection());
            System.out.println("Connection to SQLite has been established.");
            Statement stmt = ds.getConnection().createStatement();
            String sql = "SELECT * FROM employee";
            List<Entity> entityList = Db.use(ds, new Sqlite3Dialect()).query(sql);

            System.out.println("===db query======" + JSON.toJSONString(entityList));
            ResultSet rs = stmt.executeQuery(sql);

            // loop through the result set
            while (rs.next()) {
                //empid  deptid  salary
                System.out.println(rs.getInt("empid") + "\t" + rs.getInt("deptid") + "\t" + rs.getDouble("salary"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {

        }
    }


    public void query() throws SQLException {

        String sql = "select * from BAS_USER limit 5";
        List<BasUser> list = Db.use(ds).query(sql, BasUser.class, null);
        System.out.printf("***" + JSON.toJSONString(list));

    }

    public void objInsert() throws SQLException {
        BasUser basUser = new BasUser();
        basUser.setId(100);
        basUser.setUserName("张三");
        basUser.setAge(77);
        basUser.setSalary(33.3);
        int cnt = Db.use(ds).insert(Entity.create("BAS_USER").parseBean(basUser, true, true));
        System.out.println(" cnt:" + cnt);
    }

    public void insert() throws SQLException {

        for (int i = 2; i < 100; i++) {
            Entity entity = Entity.create("BAS_USER")
                    .set("ID", i)
                    .set("USER_NAME", "谷海江" + i)
                    .set("AGE", 10 + i).set("SALARY", 69.23);

            Long cnt = Db.use(ds).insertForGeneratedKey(entity);
            System.out.printf(" 插入条数：" + cnt);
        }

    }


    public static void main1(String[] args) throws SQLException {
        //new Sqlite3Demo().query();
        BasUser basUser = new BasUser();
        basUser.setId(100);
        basUser.setUserName("张三");
        basUser.setAge(77);
        basUser.setSalary(33.3);
        basUser.setMofDivId("1000");
        basUser.setMofDivCode("P001");
        BasUser bs = Entity.create("BAS_USER")
                .set("ID", 100)
                .set("USER_NAME", "谷海江")
                .set("AGE", 10)
                .set("SALARY", 69.23)
                .toBeanIgnoreCase(BasUser.class);

        Entity entity = Entity.create("BAS_USER").parseBean(basUser, true, true);


        System.out.println(entity);


    }

    public static void main(String[] args) throws SQLException {
        new Sqlite3Demo().query();
    }
}
